<?php
defined( "AZ_EXE_" ) or die ( "CORE VARIABLE IS NOT DEFINED." );
	class FeedbackModel extends Model {
		private $queryGetRow = "";
		public function display( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("title_en","email", "status","comment") , $sortColumn="created" , $sortCriteria="ASC" ){
			
			$start = is_numeric($start)&&$start>-1?$start:0;
			$perpage = is_numeric($perpage)&&$perpage>-1?$perpage:20;
			
			$result = array();
			$query = "SELECT f.`id`, f.`ref_number`, f.`comment`,
				u.`email`,
				a.`id` as article_id, a.`title_en` as article_title,
				IF(f.`status` = 0,'Block',IF(f.`status` = 1,'Private','Public')) as status, IF(f.`reply` = 0,'Pending','Replied') as response,f.`created`
			FROM az_article_feedbacks as f INNER JOIN az_articles as a ON f.`article_id` = a.`id` INNER JOIN users as u ON u.id = f.user_id  " ;
			if ( $searchString != "" ){
				$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("title_en", "email","status","comment") : $searchColumns ; 
				if ( is_array($searchColumns) && !empty($searchColumns) ){
				$conditions = "" ;
					$conditions = array() ;
					foreach( $searchColumns AS $columnName ){
						$conditions[] = " $columnName LIKE '%$searchString%' " ;
					}
					$conditions = " WHERE " . implode("OR", $conditions );
					$sortColumn = $sortColumn===""||$sortColumn===false?"f.created":$sortColumn;
					$sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
					$query .= "$conditions " ;
				}
			}
			if( $sortColumn != "" || $sortColumn != false ){
				$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"DESC";
				$query .= " ORDER BY $sortColumn $sortCriteria " ;
			}
			$this->queryGetRow = $query ;
			$query .= " LIMIT ".($start*$perpage)." , ".$perpage."; " ;
			//echo $query;
			$result = $this->executeQueryAsObject( $query ) ;
			return $result;
		}
		public function getTotalRecords(){
			return $this->getRowWithQuery($this->queryGetRow) ;
		}
		public function getFeedback($id = 0){
			$query = "SELECT f.`id`, f.`ref_number`, f.`comment`, f.`status`,f.`comment_response`,
			CONCAT(CONCAT(u.`first_name`,' '),u.`last_name`) as fullname,
			u.`email`,
			a.`id` as article_id, a.`title_en` as article_title
			FROM az_article_feedbacks as f INNER JOIN az_articles as a ON f.`article_id` = a.`id`  INNER JOIN users as u ON u.id = f.user_id
			WHERE f.`id` = '$id'" ;
			return $this->executeQueryAsObject($query);
		}
		public function getFeedbackRef($id = 0){
			$query = "SELECT f.`ref_number`
			FROM az_article_feedbacks as f
			WHERE f.`id` = '$id'" ;
			return $this->executeQueryAsObject($query);
		}
		public function replyComment($data = array(),$type){
			$fields = "";
			if($type == 0){
				$pre = "FBB"; // Feedback blocked
			}else if($type == 1){
				$pre = "FPV"; // Feedback private
			}else if($type == 2){
				$pre = "FPB"; // Feedback public
			}
			$ref = $this->getRef($type,$pre);
			foreach($data as $table_name => $all_fields){
				$query = "UPDATE " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `ref_number` = '" . $ref . "', `modified` = '" . date('Y-m-d H:i:s') . "' WHERE `id` = " . $data[$table_name]['id'];//die($query);
				return $this->executeQuery($query);
			}
		}
		public function addFeedback($user_id = 0, $aid = 0,$comment = ""){
		    $comment = $this->sqlRealEscape($comment);
			$query = "
				INSERT INTO `az_article_feedbacks` SET
				`article_id` = '" . $aid . "', `comment` = '" . $comment . "', `user_id` = " . $user_id . ", `created` = '" . date('Y-m-d H:i:s') . "'
			";
			return $this->executeQuery($query);
		}
		public function getRef($type = "", $pre = "", $increase = 1){// type: 0 = block, 1 = private, and 2 = public
			$order_number = 1;
			$sql = "SELECT `ref_number` FROM `az_article_feedbacks` WHERE `status` = '" . $type . "' ORDER By `ref_number` DESC LIMIT 1";
			$result = $this->executeQueryAsObject($sql);
			if($result[0]->ref_number != ""){
				$ref = $result[0]->ref_number;
				$order_number = substr(strstr($ref,"-"),1);
				if($order_number < 99999){
					$order_number +=$increase;
				}else{
					$order_number = 1;
				}
			}
			return $pre . "-" . sprintf("%05d",$order_number);
		}		
	}
?>